package edu.bw.dao.impl;

import edu.bw.dao.RoleDao;
import edu.bw.dto.InsertRoleForm;
import edu.bw.dto.SelectRoleByConditionForm;
import edu.bw.dto.UpdateRoleForm;
import edu.bw.pojo.Role;
import edu.bw.utils.JDBCUtils;
import edu.bw.utils.PageUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

public class RoleDaoImpl implements RoleDao {
    QueryRunner qr=new QueryRunner(JDBCUtils.getDataSource());

    @Override
    public List<Map<String,Object>> selectRoleByCondition(SelectRoleByConditionForm selectRoleByConditionForm) {
        StringBuilder sql = new StringBuilder("SELECT r.id ,r.role_name AS roleName ,JSON_LENGTH(r.permissions) AS permissions ,COUNT(u.id) AS users ,r.`desc`,r.systemic FROM  bw_zzy_role r  LEFT JOIN bw_zzy_users u ON JSON_CONTAINS(u.role, CONVERT(r.id,CHAR)) WHERE 1=1 ");
        List<Object> queryParams = new ArrayList<>();
        if (selectRoleByConditionForm.getRoleName()!=null&&!selectRoleByConditionForm.getRoleName().isEmpty()){
            sql.append(" AND r.role_name LIKE ? ");
            queryParams.add("%"+selectRoleByConditionForm.getRoleName()+"%");
        }
        sql.append("GROUP BY r.id ORDER BY r.id LIMIT ?,?");
        queryParams.add((selectRoleByConditionForm.getPage()-1)* selectRoleByConditionForm.getLength());
        queryParams.add(selectRoleByConditionForm.getLength());
        try {
            return qr.query(sql.toString(),new MapListHandler(),queryParams.toArray());
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public Long selectRoleByConditionCount(SelectRoleByConditionForm selectRoleByConditionForm) {
        StringBuilder sql = new StringBuilder("SELECT COUNT(*) FROM( SELECT r.role_name AS roleName ,JSON_LENGTH(r.permissions) AS permissions ,COUNT(u.id) AS users ,r.`desc`,r.systemic FROM  bw_zzy_role r  LEFT JOIN bw_zzy_users u ON JSON_CONTAINS(u.role, CONVERT(r.id,CHAR)) WHERE 1=1 ");
        List<Object> queryParams = new ArrayList<>();
        if (selectRoleByConditionForm.getRoleName()!=null&&!selectRoleByConditionForm.getRoleName().isEmpty()){
            sql.append(" AND r.role_name LIKE ? ");
            queryParams.add("%"+selectRoleByConditionForm.getRoleName()+"%");
        }
        sql.append(" GROUP BY r.id )AS emp ");

        try {
            return qr.query(sql.toString(),new ScalarHandler<Long>(),queryParams.toArray());
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public Integer insert(Role role) {
        StringBuilder sql = new StringBuilder("INSERT INTO bw_zzy_role SET role_name=? ,permissions=?  ");
        List<Object> insertParams = new ArrayList<>();

        insertParams.add(role.getRoleName());
        insertParams.add(role.getPermissions());

        if (role.getDesc()!=null&&!role.getDesc().isEmpty()){
            sql.append(",`desc`=? ");
            insertParams.add(role.getDesc());
        }

        try {
            return qr.update(sql.toString(),insertParams.toArray());
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }

    }

    @Override
    public List<Map<String, Object>> selectById(Integer id) {
        try {
            return qr.query("SELECT id,role_name AS roleName ,permissions ,`desc`,default_permissions AS defaultPermissions FROM bw_zzy_role  WHERE id=? ",new MapListHandler(),id);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public Integer update(UpdateRoleForm updateRoleForm) {
        try {
            return qr.update("UPDATE bw_zzy_role SET role_name=? ,`desc`=? ,permissions=?  WHERE id=?",
                    updateRoleForm.getRoleName(),
                    updateRoleForm.getDesc(),
                    updateRoleForm.getPermissions(),
                    updateRoleForm.getId());
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public boolean selectCanDelete(List<Integer> ids) {
        StringBuilder sql = new StringBuilder("SELECT  IF ( SUM( temp.users ) > 0, FALSE, TRUE ) AS result  FROM ( SELECT COUNT( u.id ) AS users  FROM bw_zzy_role r INNER JOIN bw_zzy_users u ON JSON_CONTAINS( u.role, CONVERT ( r.id, CHAR ) )  WHERE r.id IN ( ");

        sql.append(ids.stream().map(id->"?").collect(Collectors.joining(",")));

        sql.append(" )  GROUP BY r.id  ) temp ");

        try {
            Long query = qr.query(sql.toString(), new ScalarHandler<Long>(), ids.toArray());

            return query ==1?true :false;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }


    }

    @Override
    public Integer deleteRoleByIds(List<Integer> ids) {
        StringBuilder sql = new StringBuilder("DELETE FROM bw_zzy_role WHERE id IN (  ");

        sql.append(ids.stream().map(id->"?").collect(Collectors.joining(",")));
        sql.append(" ) AND systemic=FALSE ");

        try {
            return qr.update(sql.toString(),ids.toArray());
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }

    }

    @Override
    public List<Map<String, Object>> selectAllRole() {
        try {
            return qr.query("SELECT id, role_name AS roleName \n" +
                    "FROM bw_zzy_role \n" +
                    "ORDER BY id", new MapListHandler());
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
}
